package gov.nhso.prov.acc.apps.acc7;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import com.isud.paging.PageBean;

import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","unchecked","deprecation"})
public class ACC70402 extends Utility{
	public String url = "/apps/acc7/ACC70402A.jsp";

	public ACC70402(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		
		System.out.println("File name > ACC70402" );
		System.out.println("Process name >  search");
		System.out.println("Job > " + job);
		System.out.println("Line > 37");
		
		if(job.equals("init")){
			this.init(request);
		} else if(job.equals("search")){
			this.search(request);
		} else if(job.equals("export")){
			this.export(request, response);
		}
	}
	
	private String init(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "search");
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request) {
		
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";
			StringBuffer sb = new StringBuffer();
			
			String provinceId = this.getAttr(request, "provinceId");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND APA.PROVINCE_ID = '" + provinceId + "' ";
			}

			pageBean.setOrderField(" NHSO_ZONE ,  PROVINCE_ID ");
			
			sb.append("SELECT * FROM ( ");
		    
			sb.append(" SELECT PV.NHSO_ZONE , PV.NHSO_ZONENAME ,   APA.PROVINCE_ID  , APA.PROVINCE_NAME  ,  ");
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'B' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('"+endDate+"', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_B ,  "); 

			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'S' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('"+  startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('" + endDate +"', 'YYYYMMDD') )  ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_S , ");

			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'I' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('"+ endDate +"', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_I , "); 

			sb.append(" SUM( ");
			sb.append(" CASE WHEN  "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'E' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.EXPENSE_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.EXPENSE_DATE <= TO_DATE('" + endDate+ "', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.EXPENSE_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_E ");

			sb.append(" FROM acc7_transaction TRN ");
			sb.append(" INNER JOIN acc7_province_account APA ON TRN.PROVINCE_ID = APA.PROVINCE_ID AND APA.ACTIVE = 'Y' ");
			sb.append(" LEFT OUTER JOIN PROVINCE PV ON APA.PROVINCE_ID = PV.PROVINCE_ID ");
			
			sb.append(" WHERE 1 > 0 ");
			
			sb.append(condition);
			
			sb.append(" GROUP BY   PV.NHSO_ZONE , PV.NHSO_ZONENAME  ,  APA.PROVINCE_ID  , APA.PROVINCE_NAME ");
			sb.append(" ORDER BY  PV.NHSO_ZONE  ASC  ,  APA.PROVINCE_ID  ASC ");
			
			 sb.append(" ) ");
			
			
			 pageBean.setOrderType("ASC");
			
			// show  statement  SQl 
			System.out.println("1797  >> sb.toString() >> " + sb.toString());
			
			pageBean = dao.selectPage(sb.toString(), pageBean);

			if (pageBean.getPageData() == null) {
				this.message(request).setMsgCode("I001");
			}
			
			request.setAttribute("pageBean", pageBean);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("startDate", this.getAttr(request, "startDate"));
			request.setAttribute("endDate", this.getAttr(request, "endDate"));
			request.setAttribute("lsBudgetYear", this.dataUtil(dao).lsBudgetYear());
			
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
			dao.printMessage();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				// this.url = "/apps/acc6report/ACC6REPORT012.jsp";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
		
	}
	
	private void export(HttpServletRequest request,
			HttpServletResponse response) {
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			StringBuffer sb = new StringBuffer();
			
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			PageBean pageBean = new PageBean();
			String condition = "";
			String provinceId = this.getAttr(request, "provinceId");
			String startDate = this.getAttr(request, "startDate");
			String endDate = this.getAttr(request, "endDate");
			
			startDate = this.dateUtil().changeFormatDate2YYYYMMDD(startDate, "/", "");
			endDate = this.dateUtil().changeFormatDate2YYYYMMDD(endDate, "/", "");
				
			this.beanUtil().copyRequest2Bean(request, pageBean);
			
			List listAllocate = new ArrayList();
			
			if (provinceId != null && !provinceId.equals("")) {
				condition = " AND APA.PROVINCE_ID = '" + provinceId + "' ";
			}

			pageBean.setOrderField(" NHSO_ZONE , PROVINCE_ID ");
			
			sb.append("SELECT * FROM ( ");
		    
			sb.append(" SELECT PV.NHSO_ZONE , PV.NHSO_ZONENAME ,   APA.PROVINCE_ID  , APA.PROVINCE_NAME  ,  ");
			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'B' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('"+endDate+"', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_B ,  "); 

			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'S' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('"+  startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('" + endDate +"', 'YYYYMMDD') )  ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_S , ");

			sb.append(" SUM( ");
			sb.append(" CASE WHEN "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'I' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.INCOME_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.INCOME_DATE <= TO_DATE('"+ endDate +"', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.INCOME_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_I , "); 

			sb.append(" SUM( ");
			sb.append(" CASE WHEN  "); 
			sb.append(" TRN.TRANSACTION_TYPE = 'E' AND TRN.REASON_CODE  = '00' "); 
			sb.append(" AND ( TRN.EXPENSE_DATE >= TO_DATE('" + startDate + "', 'YYYYMMDD') AND TRN.EXPENSE_DATE <= TO_DATE('" + endDate+ "', 'YYYYMMDD') ) ");
			sb.append(" THEN TRN.EXPENSE_AMOUNT ELSE 0  END ");
			sb.append(" ) AS TYPE_E ");

			sb.append(" FROM acc7_transaction TRN ");
			sb.append(" INNER JOIN acc7_province_account APA ON TRN.PROVINCE_ID = APA.PROVINCE_ID AND APA.ACTIVE = 'Y' ");
			sb.append(" LEFT OUTER JOIN PROVINCE PV ON APA.PROVINCE_ID = PV.PROVINCE_ID ");
			
			sb.append(" WHERE 1 > 0 ");
			
			sb.append(condition);
			
			sb.append(" GROUP BY   PV.NHSO_ZONE , PV.NHSO_ZONENAME  ,  APA.PROVINCE_ID  , APA.PROVINCE_NAME ");
			sb.append(" ORDER BY  PV.NHSO_ZONE ASC  , APA.PROVINCE_ID  ASC ");
			
			 sb.append(" ) ");
			
			pageBean.setOrderType("ASC");
 			
 			System.out.println("export sql  > " + sb.toString());
			
			listAllocate = dao.listDataSql(sb.toString());
			
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition","attachment; filename=\"ACC7- " + (provinceId.equals("") ? "_All" : provinceId) + ".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("sheet 0");
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFCellStyle cellStyleAlignRigth = wb.createCellStyle();
			cellStyleAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			
			// Create Row
			HSSFRow rowHead = sheet.createRow((short)0);
			HSSFCell cell1Head = rowHead.createCell(0);
			HSSFCell cell2Head = rowHead.createCell(1);
			
			cell1Head.setCellValue("รายงานสรุปสถานะบัญชี 7");
			cell2Head.setCellValue("ข้อมูลระหว่าง วันที่  " + this.dateUtil().changeBcBakToAd(this.getAttr(request, "startDate"))  + "   ถึง วันที่ " +this.dateUtil().changeBcBakToAd(this.getAttr(request, "endDate")));
			
			// Create Row
			HSSFRow rowHeadSub = sheet.createRow((short)1);
			HSSFCell cell1HeadSub = rowHeadSub.createCell(0);
			// 	HSSFCell cell2HeadSub = rowHeadSub.createCell(1);
			
			SimpleDateFormat  forDateFormat  = new SimpleDateFormat("dd/MM/yyyy  hh:mm:ss");
			cell1HeadSub.setCellValue(" พิมพ์ ณ วันที่ " +  forDateFormat.format(new Date()));
			
			HSSFRow row = sheet.createRow((short)2);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);
			HSSFCell cell6 = row.createCell(5);
			HSSFCell cell7 = row.createCell(6);
			HSSFCell cell8 = row.createCell(7);
			HSSFCell cell9 = row.createCell(8);
			
			
			sheet.setDefaultColumnWidth((short) 20);
			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyle);
			cell6.setCellStyle(cellStyle);
			cell7.setCellStyle(cellStyle);
			cell8.setCellStyle(cellStyle);
			cell9.setCellStyle(cellStyle);
			
			cell1.setCellValue("รหัสเขต");
			cell2.setCellValue("เขต");
			cell3.setCellValue("รหัสจังหวัด");
			cell4.setCellValue("จังหวัด");
			cell5.setCellValue("ยอดยกมา");
			cell6.setCellValue("รับโอนSAP");
			cell7.setCellValue("รายรับ");
			cell8.setCellValue("รายจ่าย");
			cell9.setCellValue("คงเหลือ");
			
			for (int i = 1 ; i <= listAllocate.size();i++){
				
				Map map = new HashMap();
				map = (Map) listAllocate.get(i-1);
			
				row = sheet.createRow((short) (i + 2));
				cell1 = row.createCell(0);
				cell2 = row.createCell(1);
				cell3 = row.createCell(2);
				cell4 = row.createCell(3);
				cell5 = row.createCell(4);
				cell6 = row.createCell(5);
				cell7 = row.createCell(6);
				cell8 = row.createCell(7);
				cell9 = row.createCell(8);
				
				cell1.setCellValue(dao.chkData(map.get("NHSO_ZONE")));
				cell2.setCellValue(dao.chkData(map.get("NHSO_ZONENAME")));
				cell3.setCellValue(dao.chkData(map.get("PROVINCE_ID")));
				cell4.setCellValue(dao.chkData(map.get("PROVINCE_NAME")));
				cell5.setCellValue(dao.chkData(map.get("TYPE_B")));
				cell6.setCellValue(dao.chkData(map.get("TYPE_S")));
				cell7.setCellValue(dao.chkData(map.get("TYPE_I")));
				cell8.setCellValue(dao.chkData(map.get("TYPE_E")));
				
				double  amounTotal = ( Double.parseDouble(dao.chkData(map.get("TYPE_B")))  + Double.parseDouble(dao.chkData(map.get("TYPE_S")))  + Double.parseDouble(dao.chkData(map.get("TYPE_I")))  ) - Double.parseDouble(dao.chkData(map.get("TYPE_E"))) ;
				
				cell9.setCellValue(amounTotal);
				
				amounTotal = 0.0;
				
			}
				
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	
}